<?php

namespace backend\services\echarts;

use backend\extensions\ApiBaseService;
use common\models\hyg\OrderInfo;
use common\models\hyg\OrderGoods;
use common\models\hyg\RefundInfo;
use common\models\hyg\User;
use common\models\hyg\Channel;
use common\models\hyg\WechatVisitTrend;
use common\services\cache\CommonCache;

/**
 * Desc 订单相关图表服务
 * @author HUI
 */
class OrderService extends ApiBaseService {

    /**
     * 订单数据汇总（销售额，优惠金额，订单数量，客单价，新增用户）-头部汇总
     * @param int $platform_id 平台ID
     * * */
    public static function HeaderSummary($platform_id) {
        //自营
        if ($platform_id == 1) {
            $platform_id = [1, 2, 3, 100, 101];
        }
        //今日
        $day_start = strtotime(date("Y-m-d 00:00:00"));
        $day_end = strtotime(date("Y-m-d 23:59:59"));
        //本月
        $month_start = strtotime(date("Y-m-01 00:00:00"));
        $month_end = strtotime(date("Y-m-d 23:59:59"));
        //本年度
        $year_start = strtotime(date("Y-01-01 00:00:00"));
        $year_end = strtotime(date("Y-m-d 23:59:59"));
        $order = OrderInfo::find()
                ->select([
                    "ifnull(sum(if(create_time >= '{$day_start}' and create_time<= '{$day_end}' ,real_pay_money,0)),0) as amount_today",
                    "ifnull(sum(if(create_time >= '{$month_start}' and create_time<= '{$month_end}' ,real_pay_money,0)),0) as amount_month",
                    "ifnull(sum(if(create_time >= '{$year_start}' and create_time<= '{$year_end}' ,real_pay_money,0)),0) as amount_year",
                    "ifnull(sum(if(create_time >= '{$day_start}' and create_time<= '{$day_end}' ,discounts,0)),0) as discount_amount_today",
                    "ifnull(sum(if(create_time >= '{$month_start}' and create_time<= '{$month_end}' ,discounts,0)),0) as discount_amount_month",
                    "ifnull(sum(if(create_time >= '{$year_start}' and create_time<= '{$year_end}' ,discounts,0)),0) as discount_amount_year",
                    "ifnull(sum(if(create_time >= '{$day_start}' and create_time<= '{$day_end}' ,1,0)),0) as total_today",
                    "ifnull(sum(if(create_time >= '{$month_start}' and create_time<= '{$month_end}' ,1,0)),0) as total_month",
                    "ifnull(sum(if(create_time >= '{$year_start}' and create_time<= '{$year_end}' ,1,0)),0) as total_year",
                    "ifnull(count(distinct if(create_time >= '{$day_start}' and create_time<= '{$day_end}' ,user_id,0)),0) as user_total_today",
                    "ifnull(count(distinct if(create_time >= '{$month_start}' and create_time<= '{$month_end}' ,user_id,0)),0) as user_total_month",
                    "ifnull(count(distinct if(create_time >= '{$year_start}' and create_time<= '{$year_end}' ,user_id,0)),0) as user_total_year",
                ])
                ->where(['platform_id' => $platform_id, 'pay_status' => 1])
                ->asArray()
                ->one();
        $user = User::find()
                ->select([
                    "ifnull(sum(if(create_time >= '{$day_start}' and create_time<= '{$day_end}' ,1,0)),0) as user_total_today",
                    "ifnull(sum(if(create_time >= '{$month_start}' and create_time<= '{$month_end}' ,1,0)),0) as user_total_month",
                    "ifnull(sum(if(create_time >= '{$year_start}' and create_time<= '{$year_end}' ,1,0)),0) as user_total_year",
                ])
                ->where(['NOT IN', 'register_pid', [8961, 8960, 8957, 8956, 8955, 8954, 8953, 8952, 8948, 8945]])
                ->asArray()
                ->one();

        $order['unit_amount_today'] = $order['amount_today'] == 0 ? '0' : strval(round($order['amount_today'] / $order['user_total_today'], 2));
        $order['unit_amount_month'] = $order['amount_month'] == 0 ? '0' : strval(round($order['amount_month'] / $order['user_total_month'], 2));
        $order['unit_amount_year'] = $order['amount_year'] == 0 ? '0' : strval(round($order['amount_year'] / $order['user_total_year'], 2));
        unset($order['user_total_today']);
        unset($order['user_total_month']);
        unset($order['user_total_year']);
        return array_merge($order, $user);
    }

    /**
     * 获取订单/退款图表
     * https://echarts.apache.org/examples/zh/editor.html?c=line-stack
     * https://echarts.apache.org/examples/zh/editor.html?c=line-smooth
     * * */
    public static function OrderSales($platform_id) {
        //自营
        if ($platform_id == 1) {
            $platform_id = [1, 2, 3, 100, 101];
        }
        if (!is_array($platform_id)) {
            $platform_id[] = $platform_id;
        }
//        $data = CommonCache::getCache('ADMIN_ECHARTS_ORDER_SALES');
//        if ($data) {
//            return $data;
//        }
        $start = strtotime(date('Y-m-d 00:00:00', strtotime("-29 day")));
        $end = strtotime(date('Y-m-d 23:59:59'));

        $category = \common\tools\Util::getDatesByTimes($start, $end, 2);

        //订单
        $order = OrderInfo::find()
                ->select(['id', 'real_pay_money', 'order_status', 'create_time', 'user_id'])
                ->where(['AND', ['IN', 'platform_id', $platform_id], ['=', 'pay_status', 1], ['>=', 'create_time', $start], ['<=', 'create_time', $end]])
                ->asArray()
                ->all();
        $order_amount = [];
        $order_unit_price = [];
        foreach ($order as $value) {
            $date = date('m-d', $value['create_time']);
            $order_amount[$date] = !isset($order_amount[$date]) ? $value['real_pay_money'] : strval(round(($order_amount[$date] + $value['real_pay_money']), 2));
            $order_unit_price[$date]['count'] += isset($order_amount[$date][$value['user_id']]) ? 0 : 1;
            $order_unit_price[$date][$value['user_id']] = 1;
        }
        //退款
        $refund = RefundInfo::find()
                ->where(['AND', ['>=', 'create_time', $start], ['<=', 'create_time', $end], ['IN', 'refund_status', [1, 3, 4, 5, 14, 15, 18]]])
                ->asArray()
                ->all();
        $refund_amount = [];
        foreach ($refund as $value) {
            if ($value['taoxi_orderid'] || $value['doudian_aftersale_id'] || $value['ks_aftersale_id'] || $value['pdd_aftersale_id']) {
                continue;
            }
            $refund_amount[date('m-d', $value['create_time'])] = !isset($refund_amount[date('m-d', $value['create_time'])]) ? $value['refund_money'] : strval(round(($refund_amount[date('m-d', $value['create_time'])] + $value['refund_money']), 2));
        }

        
        $order_list = [];
        $refund_list = [];
        
        //数据处理
        foreach ($category as $value) {
            $order_list[$value] = [
                'key'=>$value,
                'value'=>!isset($order_amount[$value])?'0':$order_amount[$value]
            ];
            $refund_list[$value] = [
                'key'=>$value,
                'value'=>!isset($refund_amount[$value])?'0':$refund_amount[$value]
            ];
            if (!isset($order_unit_price[$value])) {
                $order_unit_price[$value]['count'] = '0';
            }
        }
        
        $data['order_sales'][] = [
            'name' => '销售金额',
            'list' => array_values($order_list)
        ];
        
        $data['order_sales'][] = [
            'name' => '退款金额',
            'list' => array_values($refund_list)
        ];

        $unit_price_list = [];
        foreach ($order_unit_price as $key => $value) {
            $unit_price_list[$key] = [
                'key' => $key,
                'value' => $value['count'] == 0 ? '0' : strval(round($order_amount[$key] / $value['count'], 2))
            ];
        }
        $data['order_unit_price'][] = [
            'name'=>'',
            'list'=>array_values($unit_price_list)
        ];
        CommonCache::setCache('ADMIN_ECHARTS_ORDER_SALES', $data, 10 * 60);
        return $data;
    }

    /**
     * 获取新增用户
     * https://echarts.apache.org/examples/zh/editor.html?c=pictorialBar-bar-transition
     * * */
    public static function newUser($platform_id) {
        //自营
        if ($platform_id == 1) {
            $platform_id = [1, 2, 3, 100, 101];
        }
        if (!is_array($platform_id)) {
            $platform_id[] = $platform_id;
        }
//        $data = CommonCache::getCache('ADMIN_ECHARTS_NEW_USER');
//        if ($data) {
//            return $data;
//        }
        $start = strtotime(date('Y-m-d 00:00:00', strtotime("-29 day")));
        $end = strtotime(date('Y-m-d 23:59:59'));

        $category = \common\tools\Util::getDatesByTimes($start, $end, 2);

        //订单
        $user = User::find()
                ->select(['id', 'create_time'])
                ->where(['AND', ['>=', 'create_time', $start], ['<=', 'create_time', $end], ['NOT IN', 'register_pid', [8961, 8960, 8957, 8956, 8955, 8954, 8953, 8952, 8948, 8945]]])
                ->asArray()
                ->all();
        $list = [];
        foreach ($user as $value) {
            $date = date('m-d', $value['create_time']);
            $list[$date] = !isset($list[$date]) ? '1' : strval(($list[$date] + 1));
        }

        
        //数据处理
        $new_user = [];
        foreach ($category as $value) {
            $new_user[$value] = [
                'key'=>$value,
                'value'=>!isset($list[$value])?'0':$list[$value]
            ];
        }
        $data['new_user'][] = [
            'name'=>'',
            'list'=>array_values($new_user)
        ];
        CommonCache::setCache('ADMIN_ECHARTS_NEW_USER', $data, 10 * 60);
        return $data;
    }

    /**
     * 获取渠道统计
     * https://echarts.apache.org/examples/zh/editor.html?c=pictorialBar-bar-transition
     * * */
    public static function channel($platform_id) {
        //自营
        if ($platform_id == 1) {
            $platform_id = [1, 2, 3, 100, 101];
        }

//        $data = CommonCache::getCache('ADMIN_ECHARTS_CHANNEL');
//        if ($data) {
//            return $data;
//        }
                //昨日
        $yesterday_start = strtotime(date('Y-m-d 00:00:00', strtotime("-1 day")));
        $yesterday_end = strtotime(date("Y-m-d 23:59:59", $yesterday_start));
        //今日
        $day_start = strtotime(date("Y-m-d 00:00:00"));
        $day_end = strtotime(date("Y-m-d 23:59:59"));
        //本月
        $month_start = strtotime(date("Y-m-01 00:00:00"));
        $month_end = strtotime(date("Y-m-d 23:59:59"));
        //本年度
        $year_start = strtotime(date("Y-01-01 00:00:00"));
        $year_end = strtotime(date("Y-m-d 23:59:59"));

                $order = OrderInfo::find()
                ->select([
                    "mid",
                    "ifnull(sum(if(create_time >= '{$yesterday_start}' and create_time<= '{$yesterday_end}' ,real_pay_money,0)),0) as amount_yesterday",
                    "ifnull(sum(if(create_time >= '{$day_start}' and create_time<= '{$day_end}' ,real_pay_money,0)),0) as amount_today",
                    "ifnull(sum(if(create_time >= '{$month_start}' and create_time<= '{$month_end}' ,real_pay_money,0)),0) as amount_month",
                    "ifnull(sum(if(create_time >= '{$year_start}' and create_time<= '{$year_end}' ,real_pay_money,0)),0) as amount_year",
                ])
                ->where(['platform_id' => $platform_id, 'pay_status' => 1])
                ->groupBy('mid')
                ->having('amount_today >0 AND amount_yesterday >0 AND amount_month>0')
                ->orderBy('amount_year desc')
                ->limit(20)
                ->asArray()
                ->all();
        $list = [];
        foreach ($order as $value) {
            $name = Channel::find()->where(['id' => $value['mid']])->one()->name;
            unset($value['mid']);
            $list[] = [
                'key' => $name,
                'value' => $value
            ];
        }
        $data['channel'][] = [
            'name' => '',
            'list' => $list,
        ];
        if ($list) {
            CommonCache::setCache('ADMIN_ECHARTS_CHANNEL', $data, 10 * 60);
        }
        return $data;
    }

    /**
     * 获取PUV统计-新零售
     * https://echarts.apache.org/examples/zh/editor.html?c=pictorialBar-dotted
     * * */
    public static function puv() {
        $user = WechatVisitTrend::find()
                        ->where(['between', 'date', date('Ymd', strtotime('-30 day')), date('Ymd', strtotime('-1 day'))])
                        ->andWhere(['type' => 1])
                        ->select(['date', 'session_cnt', 'visit_pv', 'visit_uv', 'visit_uv_new'])
                        ->orderBy('date')
                        ->asArray()->all();
        $session_cnt = [];
        $visit_pv = [];
        $visit_uv = [];
        $visit_uv_new = [];
        foreach ($user as $value) {
            $session_cnt[] = [
                'key' => $value['date'],
                'value' => $value['session_cnt'],
            ];
            $visit_pv[] = [
                'key' => $value['date'],
                'value' => $value['visit_pv'],
            ];
            $visit_uv[] = [
                'key' => $value['date'],
                'value' => $value['visit_uv'],
            ];
            $visit_uv_new[] = [
                'key' => $value['date'],
                'value' => $value['visit_uv_new'],
            ];
        }
        $data['wechat'][] = [
            'name'=>'打开次数',
            'list'=>$session_cnt
        ];
        $data['wechat'][] = [
            'name'=>'访问次数',
            'list'=>$visit_pv
        ];
        $data['wechat'][] = [
            'name'=>'访问人数',
            'list'=>$visit_uv
        ];
        $data['wechat'][] = [
            'name'=>'新用户数',
            'list'=>$visit_uv_new
        ];
        return $data;
    }
    
    /**
     * 获取渠道统计
     * https://echarts.apache.org/examples/zh/editor.html?c=dataset-series-layout-by
     * * */
    public static function goods($platform_id) {
        //自营
        if ($platform_id == 1) {
            $platform_id = [1, 2, 3, 100, 101];
        }

//        $data = CommonCache::getCache('ADMIN_ECHARTS_GOODS');
//        if ($data) {
//            return $data;
//        }

        //昨日
        $yesterday_start = strtotime(date('Y-m-d 00:00:00', strtotime("-1 day")));
        $yesterday_end = strtotime(date("Y-m-d 23:59:59", $yesterday_start));
        //今日
        $day_start = strtotime(date("Y-m-d 00:00:00"));
        $day_end = strtotime(date("Y-m-d 23:59:59"));
        //本月
        $month_start = strtotime(date("Y-m-01 00:00:00"));
        $month_end = strtotime(date("Y-m-d 23:59:59"));


        $goods = OrderGoods::find()
                ->alias('goods')
                ->select([
                    "barcode",
                    "name",
                    "cit_code",
                    "ifnull(sum(if(order.create_time >= '{$yesterday_start}' and order.create_time<= '{$yesterday_end}' ,goods.active_cut,0)),0) as amount_yesterday",
                    "ifnull(sum(if(order.create_time >= '{$day_start}' and order.create_time<= '{$day_end}' ,goods.active_cut,0)),0) as amount_today",
                    "ifnull(sum(if(order.create_time >= '{$month_start}' and order.create_time<= '{$month_end}' ,goods.active_cut,0)),0) as amount_month",
                    
                    "ifnull(sum(if(order.create_time >= '{$yesterday_start}' and order.create_time<= '{$yesterday_end}' ,goods.count,0)),0) as count_yesterday",
                    "ifnull(sum(if(order.create_time >= '{$day_start}' and order.create_time<= '{$day_end}' ,goods.count,0)),0) as count_today",
                    "ifnull(sum(if(order.create_time >= '{$month_start}' and order.create_time<= '{$month_end}' ,goods.count,0)),0) as count_month",
                ])
                ->leftJoin(OrderInfo::tableName() . ' order', 'goods.order_id=order.id')
                ->where(['order.platform_id' => $platform_id, 'order.pay_status' => 1])
                ->groupBy('goods.barcode')
                ->having('amount_month>0')
                ->asArray()
                ->all();
       
        //按今日排行
        array_multisort(array_column($goods, 'count_today'),SORT_DESC,$goods);
        $day_data = array_slice($goods, 0,10);
        foreach ($day_data as $value) {
            $amount_today[] = [
                'key' => $value['name'],
                'value' => [
                    'today' => [
                        'name' => $value['name'],
                        'barcode' => $value['barcode'],
                        'cit_code' => $value['cit_code'],
                        'count' => $value['count_today'],
                        'amount' => $value['amount_today']
                    ],
                    'yesterday' => [
                        'name' => $value['name'],
                        'barcode' => $value['barcode'],
                        'cit_code' => $value['cit_code'],
                        'count' => $value['count_today'],
                        'amount' => $value['amount_yesterday']
                    ],
                    'month' => [
                        'name' => $value['name'],
                        'barcode' => $value['barcode'],
                        'cit_code' => $value['cit_code'],
                        'count' => $value['count_today'],
                        'amount' => $value['count_month']
                    ]
                ],
            ];
        }
        $data['goods'][] = [
            'name' => '',
            'list' => $amount_today,
        ];

        //按昨日排行
        array_multisort(array_column($goods, 'count_yesterday'),SORT_DESC,$goods);
        $yesterday_data = array_slice($goods, 0,10);
        foreach ($yesterday_data as $value) {
            $amount_yesterday[] = [
                'key' => $value['name'],
                'value' => [
                    'today' => ['name' => $value['name'],
                        'barcode' => $value['barcode'],
                        'cit_code' => $value['cit_code'],
                        'count' => $value['count_today'],
                        'amount' => $value['amount_today']
                    ],
                    'yesterday' => [
                        'name' => $value['name'],
                        'barcode' => $value['barcode'],
                        'cit_code' => $value['cit_code'],
                        'count' => $value['count_yesterday'],
                        'amount' => $value['amount_yesterday']
                    ],
                    'month' => [
                        'name' => $value['name'],
                        'barcode' => $value['barcode'],
                        'cit_code' => $value['cit_code'],
                        'count' => $value['count_month'],
                        'amount' => $value['amount_month']
                    ]
                ],
            ];
        }
        
        $data['goods'][] = [
            'name' => '',
            'list' => $amount_yesterday,
        ];
        CommonCache::setCache('ADMIN_ECHARTS_GOODS', $data, 10 * 60);
        return $data;
    }

}
